DROP TABLE if exists edu_online_ads.ads_visit_consult_data_count;
create table edu_online_ads.ads_visit_consult_data_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    --指标
    visit_person      BIGINT COMMENT '访问人数',
    visit_dialogue    BIGINT COMMENT '会话数',
    visit_ip          BIGINT COMMENT 'IP数'
)comment "时间统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');

DROP TABLE if exists edu_online_ads.ads_visit_consult_data_area_count;
create table edu_online_ads.ads_visit_consult_data_area_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    country                      STRING COMMENT '所在国家',
    province                     STRING COMMENT '省',
    city                         STRING COMMENT '城市',
    --指标
    visit_person      BIGINT COMMENT '访问人数',
    visit_dialogue    BIGINT COMMENT '会话数',
    visit_ip          BIGINT COMMENT 'IP数'
)comment "地区统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');

DROP TABLE if exists edu_online_ads.ads_visit_consult_data_origin_channel_count;
create table edu_online_ads.ads_visit_consult_data_origin_channel_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    origin_channel STRING COMMENT '来源渠道',
    --指标
    visit_person      BIGINT COMMENT '访问人数',
    visit_dialogue    BIGINT COMMENT '会话数',
    visit_ip          BIGINT COMMENT 'IP数'
)comment "来源渠道统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');

DROP TABLE if exists edu_online_ads.ads_visit_consult_data_seo_source_count;
create table edu_online_ads.ads_visit_consult_data_seo_source_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    seo_source     STRING COMMENT '搜索来源',
    --指标
    visit_person      BIGINT COMMENT '访问人数',
    visit_dialogue    BIGINT COMMENT '会话数',
    visit_ip          BIGINT COMMENT 'IP数'
)comment "搜索来源统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');

DROP TABLE if exists edu_online_ads.ads_visit_consult_data_from_url_count;
create table edu_online_ads.ads_visit_consult_data_from_url_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    from_url       STRING COMMENT '来源页面',
    --指标
    visit_person      BIGINT COMMENT '访问人数',
    visit_dialogue    BIGINT COMMENT '会话数',
    visit_ip          BIGINT COMMENT 'IP数'
)comment "来源页面统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');

DROP TABLE if exists edu_online_ads.ads_visit_consult_rate_count;
create table edu_online_ads.ads_visit_consult_rate_count(
    --维度
    day            STRING COMMENT '天',
    month          STRING COMMENT '月',
    quarter        STRING COMMENT '季度',
    year           STRING COMMENT '年',
    country                      STRING COMMENT '所在国家',
    province                     STRING COMMENT '省',
    city                         STRING COMMENT '城市',
    --指标
    country_consult_rate       decimal(34,4) COMMENT '国家咨询率',
    province_consult_rate       decimal(34,4) COMMENT '省咨询率',
    city_consult_rate       decimal(34,4) COMMENT '市咨询率'
)comment "地区咨询率统计表"
row format delimited fields terminated by '\t'
stored as orc
tblproperties ('orc.compress' = 'SNAPPY');
